<?php
/**
 * Created by PhpStorm.
 * User: zhan
 * Date: 2018/9/30  10:17
 */
namespace app\admin\controller;
use think\Db;
class Loan extends Common
{
    private $table_admin = '';

    /*
	*	构造函数
	*/
    function __construct()
    {
        parent::__construct();
        $this->table_admin = db('loan_info');
    }

    //贷款列表
    public function lists()
    {
        $list = $this->table_admin->order('id desc')->paginate();
        $this->assign('list', $list);
        $this->assign('page', $list->render());
        return $this->fetch();
    }

    //导入记录
    public function record(){
        $list = db('loan_record')->order('id desc')->paginate();
        $this->assign('list', $list);
        $this->assign('page', $list->render());
        return $this->fetch();
    }

    //导入数据
    public function import()
    {
        if(request()->isAjax()){
            vendor("Excel.PHPExcel");

            $objPHPExcel = new \PHPExcel();

            //获取表单上传文件  
            $file = request()->file('file');
            $xl_file_name = input('file_name');

            if($file) {
                $info = $file->validate(['ext'=>'xlsx,xls'])->move(ROOT_PATH . 'public/static/uploads'); 
                $exclePath = $info->getSaveName();  //获取文件名
                $file_name = ROOT_PATH . 'public/static/uploads/'. $exclePath;   //上传文件的地址
                $file_suffix_name = explode('.',$exclePath);
                $file_suffix_name = $file_suffix_name[1];

                $startRow = 2;
                $endRow = 501;
                $bool = true;

                //导入记录
                $record_data['file_name'] = $xl_file_name;
                $record_data['creatime'] = time();
                db('loan_record')->insert($record_data);

                if($info){
                     //分批导入
                    do{
                        //excle处理
                        $excel_array = $this->array_excle($file_name, $file_suffix_name, $startRow, $endRow);
                       
                        //数组处理
                        $bool = $this->array_iconv($excel_array);  

                        $startRow += 500;
                        $endRow += 500;
                        ob_flush();
                        flush();
                        usleep(1000); //睡会觉，大量读取，冲击挺大
                    }while($bool);

                    $this->success("操作成功!", url('Loan/import'));
                }else{  
                    // 上传失败获取错误信息 
                    return $this->error($file->getError()); 
                }

            }else{
                return $this->error('请添加文件');
            }
        }

        return $this->fetch();
    }


    /**
    *excle处理
    */
    function array_excle($file_name, $file_suffix_name, $startRow = 1, $endRow = null){
        ini_set('memory_limit','4072M');    // 临时设置最大内存占用为3G
        set_time_limit(0);   // 设置脚本最大执行时间 为0 永不过期

        if($file_suffix_name == 'xlsx'){
            $objReader = \PHPExcel_IOFactory::createReader('Excel2007');
        }else{  
            $objReader = \PHPExcel_IOFactory::createReader('Excel5');
        }

        $objReader->setReadDataOnly(true);

        //如果有指定行数，则设置过滤器
        if ($startRow && $endRow) {
            vendor("Excel.PHPExcels");
            $perf = new \PHPExcels();
            $perf->startRow = $startRow;
            $perf->endRow   = $endRow;
            $objReader->setReadFilter($perf);
        }

        $obj_PHPExcel =$objReader->load($file_name, $encode = 'utf-8');  //加载文件内容,编码utf-8  
        $activeSheet = $obj_PHPExcel->getActiveSheet();
        $total = $activeSheet->getHighestRow(); //总行数
        
        $excel_array=$obj_PHPExcel->getsheet(0)->toArray();   //转换为数组格式
        return $excel_array;
    }

    /**
     * 对数据进行编码转换
     * @param array/string $data 数组
     * @param string $output 转换后的编码
     * Created on 2016-7-13
     */
    function array_iconv_string($data, $output = 'utf-8') {
          $encode_arr = array('UTF-8','ASCII','GBK','GB2312','BIG5','JIS','eucjp-win','sjis-win','EUC-JP');
          $encoded = mb_detect_encoding($data, $encode_arr);
          return mb_convert_encoding($data, $output, $encoded);
    }

    /**
    *数据分类
    */
    function loan_level_filter($array){
        $level_id = 0;
        if(!is_array($array))return $level_id;

        if(empty($array[1]))return $level_id;
        
        if(!empty($array[7]) && !empty($array[8]) && !empty($array[12]))return $level_id = 1;
        if(!empty($array[9]) && !empty($array[10]) && !empty($array[13]))return $level_id = 2;

        return $level_id = 3;
    }
    
    /**
     *数组处理
     */
    function array_iconv($array){ 
          ini_set('memory_limit','4072M');    // 临时设置最大内存占用为3G
          set_time_limit(0);   // 设置脚本最大执行时间 为0 永不过期
          $data = array();
            
          if(is_array($array)){
            foreach ($array as $key => &$value) {
                $value = implode("|",$value);

                $value = $this->array_iconv_string($value);
                $string = '';

                $value = explode("|", $value);

                if(empty($value[0])){
                    continue;
                }

                //去除空格
                foreach ($value as $k => &$v) {
                    $v = trim($v);
                    //将空格的名称替换掉
                    $v = str_replace(" ","", $v);
                }

                $data[$key]['name'] = !empty($value[0])?$value[0]:'';
                $data[$key]['mobile'] = !empty($value[1])?$value[1]:'';
                $data[$key]['city'] = !empty($value[2])?$value[2]:'';
                $data[$key]['gender'] = !empty($value[3])?$value[3]:'';
                $data[$key]['age'] = !empty($value[4])?$value[4]:'';
                $data[$key]['loan_amount'] = !empty($value[5])?$value[5]:'';
                $data[$key]['credit_card'] = !empty($value[6])?$value[6]:'';
                $data[$key]['property'] = !empty($value[7])?$value[7]:'';
                $data[$key]['car_production'] = !empty($value[8])?$value[8]:'';
                $data[$key]['career'] = !empty($value[9])?$value[9]:'';
                $data[$key]['pay_method'] = !empty($value[10])?$value[10]:'';
                $data[$key]['income'] = !empty($value[11])?$value[11]:'';
                $data[$key]['provident_fund'] = !empty($value[12])?$value[12]:'';
                $data[$key]['social_security'] = !empty($value[13])?$value[13]:'';
                $data[$key]['loan_term'] = !empty($value[14])?$value[14]:'';

                $data[$key]['level_id'] = $this->loan_level_filter($value);
            }
            
            db('loan_info')->insertAll($data);

            if(!empty($data)){
                return true;
            }else{
                return false;
            }
        }else{
            return false;
        }
    }

    //数据分类
    public function category(){
        $id = input('id');
        $loan_level = db('loan_level')->where(['id'=>$id])->find();

        if(request()->isAjax()){
            $price = input('price');
            $id = input('id');

            $date['price'] = $price;
            $res = db('loan_level')->where(['id'=>$id])->update($date);
            if(!$res)$this->error('更新失败!');
            $this->success('更新成功!',url('Loan/category_list'));
        }

        $this->assign('da',$loan_level);
        return $this->fetch();
    }

    //数据列表
    public function category_list(){
        $list = db('loan_level')->order('id asc')->paginate();
        $this->assign('list', $list);
        $this->assign('page', $list->render());
        return $this->fetch();
    }
}


